﻿IF schema_id('ae') IS NULL
    EXECUTE('CREATE SCHEMA [ae]')
CREATE TABLE [ae].[BASE_AEDetails] (
    [ID] [bigint] NOT NULL IDENTITY,
    [AEStatus] [int] NOT NULL,
    [RecordState] [int] NOT NULL,
    [LastName] [nvarchar](256),
    [FirstName] [nvarchar](256),
    [Alias] [nvarchar](256),
    [CHNName] [nvarchar](256),
    [Gender] [nvarchar](1),
    [HKID] [nvarchar](256),
    [Email] [nvarchar](256),
    [Tel] [nvarchar](32),
    [Mobile] [nvarchar](max),
    [SMS] [nvarchar](32),
    [ExtensionNo] [nvarchar](32),
    [Addr1] [nvarchar](512),
    [Addr2] [nvarchar](512),
    [Onboarding] [datetime],
    [TerminationDate] [datetime],
    [SFCCENo] [nvarchar](16),
    [LocationID] [int],
    [Remarks] [nvarchar](max),
    [ModifiedBy] [nvarchar](256),
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](256),
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_ae.BASE_AEDetails] PRIMARY KEY ([ID])
)
CREATE INDEX [IX_LocationID] ON [ae].[BASE_AEDetails]([LocationID])
CREATE TABLE [ae].[BASE_BankAccount] (
    [ID] [bigint] NOT NULL IDENTITY,
    [AEID] [bigint] NOT NULL,
    [BankName] [nvarchar](512) NOT NULL,
    [AccountNo] [nvarchar](512) NOT NULL,
    CONSTRAINT [PK_ae.BASE_BankAccount] PRIMARY KEY ([ID])
)
CREATE INDEX [IX_AEID] ON [ae].[BASE_BankAccount]([AEID])
CREATE TABLE [ae].[BASE_JoinHistories] (
    [ID] [bigint] NOT NULL IDENTITY,
    [AEID] [bigint] NOT NULL,
    [Onboarding] [datetime],
    [Termination] [datetime],
    [Remarks] [nvarchar](max),
    [ModifiedBy] [nvarchar](256),
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](256),
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_ae.BASE_JoinHistories] PRIMARY KEY ([ID])
)
CREATE INDEX [IX_AEID] ON [ae].[BASE_JoinHistories]([AEID])
CREATE TABLE [ae].[BASE_Locations] (
    [BCHCode] [nvarchar](8) NOT NULL,
    [Room] [nvarchar](32),
    [ID] [int] NOT NULL IDENTITY,
    [Seat] [nvarchar](32),
    [ModifiedBy] [nvarchar](256),
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](256),
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_ae.BASE_Locations] PRIMARY KEY ([ID])
)
CREATE UNIQUE INDEX [UIX_Location] ON [ae].[BASE_Locations]([BCHCode], [Room], [Seat])
CREATE TABLE [ae].[BASE_Branches] (
    [Code] [nvarchar](8) NOT NULL,
    [FullName] [nvarchar](256),
    [ModifiedBy] [nvarchar](256),
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](256),
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_ae.BASE_Branches] PRIMARY KEY ([Code])
)
CREATE TABLE [ae].[BASE_RoomSize] (
    [BCHCode] [nvarchar](8) NOT NULL,
    [Room] [nvarchar](32) NOT NULL,
    [Size] [decimal](18, 2),
    [ModifiedBy] [nvarchar](256),
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](256),
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_ae.BASE_RoomSize] PRIMARY KEY ([BCHCode], [Room])
)
CREATE TABLE [ae].[BASE_AEBaseInfos] (
    [AECode] [nvarchar](16) NOT NULL,
    [UserName] [nvarchar](256),
    [UserID] [uniqueidentifier],
    [DetailID] [bigint],
    [MappingType] [int],
    [MasterAECode] [nvarchar](16),
    [Costed] [bit] NOT NULL,
    [ModifiedBy] [nvarchar](256),
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](256),
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_ae.BASE_AEBaseInfos] PRIMARY KEY ([AECode])
)
CREATE INDEX [IX_DetailID] ON [ae].[BASE_AEBaseInfos]([DetailID])
CREATE TABLE [ae].[BASE_AEMappingHistories] (
    [ID] [bigint] NOT NULL IDENTITY,
    [AECode] [nvarchar](16) NOT NULL,
    [DetailID] [bigint],
    [EffectiveDate] [datetime] NOT NULL,
    [MappingType] [int],
    [MasterAECode] [nvarchar](16),
    [Costed] [bit] NOT NULL,
    [ModifiedBy] [nvarchar](256),
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](256),
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_ae.BASE_AEMappingHistories] PRIMARY KEY ([ID])
)
CREATE TABLE [ae].[BASE_AllocationHistories] (
    [ID] [bigint] NOT NULL IDENTITY,
    [AEID] [bigint] NOT NULL,
    [EffectiveDate] [datetime] NOT NULL,
    [BCHCode] [nvarchar](8),
    [Room] [nvarchar](32),
    [Seat] [nvarchar](32),
    [RoomSize] [decimal](18, 2),
    [ModifiedBy] [nvarchar](256),
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](256),
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_ae.BASE_AllocationHistories] PRIMARY KEY ([ID])
)
CREATE INDEX [IX_AEID] ON [ae].[BASE_AllocationHistories]([AEID])
CREATE TABLE [ae].[BASE_ClientAllocationHistories] (
    [ID] [bigint] NOT NULL IDENTITY,
    [ClientCode] [nvarchar](16) NOT NULL,
    [EffectiveDate] [datetime] NOT NULL,
    [BCHCode] [nvarchar](8),
    [Room] [nvarchar](32),
    [Seat] [nvarchar](32),
    [RoomSize] [decimal](18, 2),
    [ModifiedBy] [nvarchar](256),
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](256),
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_ae.BASE_ClientAllocationHistories] PRIMARY KEY ([ID])
)
CREATE INDEX [IX_ClientCode] ON [ae].[BASE_ClientAllocationHistories]([ClientCode])
CREATE TABLE [ae].[BASE_ClientDetails] (
    [ClientCode] [nvarchar](16) NOT NULL,
    [RecordState] [int] NOT NULL,
    [LocationID] [int],
    [Remarks] [nvarchar](max),
    [ModifiedBy] [nvarchar](256),
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](256),
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_ae.BASE_ClientDetails] PRIMARY KEY ([ClientCode])
)
CREATE INDEX [IX_LocationID] ON [ae].[BASE_ClientDetails]([LocationID])
CREATE TABLE [ae].[BASE_ClientAccountTypes] (
    [ClientCode] [nvarchar](16) NOT NULL,
    [AccType] [nvarchar](2) NOT NULL,
    CONSTRAINT [PK_ae.BASE_ClientAccountTypes] PRIMARY KEY ([ClientCode], [AccType])
)
CREATE INDEX [IX_ClientCode] ON [ae].[BASE_ClientAccountTypes]([ClientCode])

CREATE TABLE [dbo].[Logs] (
    [ID] [bigint] NOT NULL IDENTITY,
    [TimeStamp] [datetime],
    [Level] [nvarchar](50),
    [Host] [nvarchar](100),
    [RemoteAddr] [nvarchar](100),
    [Logger] [nvarchar](100),
    [Source] [nvarchar](100),
    [Url] [nvarchar](512),
    [Message] [nvarchar](max),
    [Exception] [nvarchar](max),
    CONSTRAINT [PK_dbo.Logs] PRIMARY KEY ([ID])
)


--CREATE TABLE [dbo].[TrackChangeLogs] (
--    [ID] [bigint] NOT NULL IDENTITY,
--    [Date] [datetime] NOT NULL,
--    [Table] [nvarchar](256) NOT NULL,
--    [User] [nvarchar](256),
--    [Action] [int] NOT NULL,
--    [ActionDesc] [nvarchar](256) NOT NULL,
--    [ObjectType] [nvarchar](800),
--    [SerializedData] [ntext],
--    CONSTRAINT [PK_dbo.TrackChangeLogs] PRIMARY KEY ([ID])
--)
CREATE TABLE [ae].[BASE_AEGroups] (
    [MasterAEID] [bigint] NOT NULL,
    [AEID] [bigint] NOT NULL,
    CONSTRAINT [PK_ae.BASE_AEGroups] PRIMARY KEY ([MasterAEID], [AEID])
)
CREATE INDEX [IX_MasterAEID] ON [ae].[BASE_AEGroups]([MasterAEID])
CREATE INDEX [IX_AEID] ON [ae].[BASE_AEGroups]([AEID])
ALTER TABLE [ae].[BASE_AEDetails] ADD CONSTRAINT [FK_ae.BASE_AEDetails_ae.BASE_Locations_LocationID] FOREIGN KEY ([LocationID]) REFERENCES [ae].[BASE_Locations] ([ID])
ALTER TABLE [ae].[BASE_BankAccount] ADD CONSTRAINT [FK_ae.BASE_BankAccount_ae.BASE_AEDetails_AEID] FOREIGN KEY ([AEID]) REFERENCES [ae].[BASE_AEDetails] ([ID])
ALTER TABLE [ae].[BASE_JoinHistories] ADD CONSTRAINT [FK_ae.BASE_JoinHistories_ae.BASE_AEDetails_AEID] FOREIGN KEY ([AEID]) REFERENCES [ae].[BASE_AEDetails] ([ID])
ALTER TABLE [ae].[BASE_Locations] ADD CONSTRAINT [FK_ae.BASE_Locations_ae.BASE_Branches_BCHCode] FOREIGN KEY ([BCHCode]) REFERENCES [ae].[BASE_Branches] ([Code])
ALTER TABLE [ae].[BASE_Locations] ADD CONSTRAINT [FK_ae.BASE_Locations_ae.BASE_RoomSize_BCHCode_Room] FOREIGN KEY ([BCHCode], [Room]) REFERENCES [ae].[BASE_RoomSize] ([BCHCode], [Room])
ALTER TABLE [ae].[BASE_AEBaseInfos] ADD CONSTRAINT [FK_ae.BASE_AEBaseInfos_ae.BASE_AEDetails_DetailID] FOREIGN KEY ([DetailID]) REFERENCES [ae].[BASE_AEDetails] ([ID])
ALTER TABLE [ae].[BASE_AllocationHistories] ADD CONSTRAINT [FK_ae.BASE_AllocationHistories_ae.BASE_AEDetails_AEID] FOREIGN KEY ([AEID]) REFERENCES [ae].[BASE_AEDetails] ([ID])
ALTER TABLE [ae].[BASE_ClientAllocationHistories] ADD CONSTRAINT [FK_ae.BASE_ClientAllocationHistories_ae.BASE_ClientDetails_ClientCode] FOREIGN KEY ([ClientCode]) REFERENCES [ae].[BASE_ClientDetails] ([ClientCode])
ALTER TABLE [ae].[BASE_ClientDetails] ADD CONSTRAINT [FK_ae.BASE_ClientDetails_ae.BASE_Locations_LocationID] FOREIGN KEY ([LocationID]) REFERENCES [ae].[BASE_Locations] ([ID])
ALTER TABLE [ae].[BASE_ClientAccountTypes] ADD CONSTRAINT [FK_ae.BASE_ClientAccountTypes_ae.BASE_ClientDetails_ClientCode] FOREIGN KEY ([ClientCode]) REFERENCES [ae].[BASE_ClientDetails] ([ClientCode])
ALTER TABLE [ae].[BASE_AEGroups] ADD CONSTRAINT [FK_ae.BASE_AEGroups_ae.BASE_AEDetails_MasterAEID] FOREIGN KEY ([MasterAEID]) REFERENCES [ae].[BASE_AEDetails] ([ID])
ALTER TABLE [ae].[BASE_AEGroups] ADD CONSTRAINT [FK_ae.BASE_AEGroups_ae.BASE_AEDetails_AEID] FOREIGN KEY ([AEID]) REFERENCES [ae].[BASE_AEDetails] ([ID])
